using System;
using System.Runtime.Remoting;
using System.Collections;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Data.Sql;
using Microsoft.SqlServer.Server;

namespace OnlinePayments
{
	public struct CustomerInfo
	{
		public string FirstName;
		public string LastName;
		public string Address;
		public string City;
		public string State;
		public string Zip;
		public string Country;
		public string CardNumber;
		public string CardExpiration;
		public string CVV2;
	}

	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class PaymentProcessor
	{
		private PaymentProcessor _paymentProcessor = null;
		protected bool _test = false;
        protected string _className = null;
        protected Dictionary<string, string> _configStrings = new Dictionary<string, string>();
        protected SqlConnection _conn;

        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ProcessTransaction(SqlInt32 transactionID, SqlBoolean failTransactionOnFailure)
        {
            SqlConnection conn = new SqlConnection(@"context connection=true");
            if (conn.State != System.Data.ConnectionState.Open)
                conn.Open();

            SqlCommand cmm = conn.CreateCommand();
            cmm.CommandText = "select tr.TransactionID, tr.TransactionTypeCode, tr.ProcessorCode, tr.Amount, acc.CardNumber, acc.CardExpiration, oh.OwnerID,\n" +
                "   per.FirstName, per.LastName, add.Address1, add.City, add.State, add.PostalCode, cou.Name,\n" +
                "   tr.TransactionStatusCode, tr.ProcessorTransactionID, tr.CardAuthorization, tr.RelatedTransactionID, tr2.ProcessorTransactionID\n" +
                "from tblTransaction tr\n" +
                "   join tblAccount acc on tr.AccountID = acc.AccountID\n" +
                "   join tblOrderHeader oh on tr.OrderID = oh.OrderID\n" +
                "   join tblCustomer cus on oh.CustomerID = cus.CustomerID\n" +
                "   join tblPerson per on cus.PersonID = per.PersonID\n" +
                "   join tblAddress add on per.AddressID = add.AddressID\n" +
                "   join codeCountry cou on add.CountryID = cou.CountryID\n" +
                "   left outer join tblTransaction tr2 on tr.RelatedTransactionID = tr2.TransactionID\n" +
                "where tr.TransactionStatusCode in ('E', 'A')\n" +
                "   and tr.TransactionID = " + transactionID.Value.ToString();
            SqlDataReader rdr = cmm.ExecuteReader();
            if (rdr.Read())
            {
                CustomerInfo ci = new CustomerInfo();
                ci.FirstName = rdr.GetString(7);
                ci.LastName = rdr.GetString(8);
                ci.Address = rdr.GetString(9);
                ci.City = rdr.GetString(10);
                ci.State = rdr.GetString(11);
                ci.Zip = rdr.GetString(12);
                ci.Country = rdr.GetString(13);
                ci.CardNumber = rdr.GetString(4);
                ci.CardExpiration = rdr.GetString(5);

                string processorCode = rdr.GetString(2);
                int ownerID = rdr.GetInt32(6);
                string transactionTypeCode = rdr.GetString(1);
                string transactionStatusCode = rdr.GetString(14);
                PaymentProcessor processor = CreatePaymentProcessor(conn, processorCode[0], ownerID);

                string responseCode = string.Empty, responseText = string.Empty, AVSResult = string.Empty, CVV2Response = string.Empty,
                    processorTransactionID = string.Empty, authCode = string.Empty;
                bool result = false;

                if (transactionTypeCode == "B" && transactionStatusCode == "E")
                {
                    result = processor.Deposit(ci, transactionID.Value, rdr.GetDecimal(3), out responseCode, out responseText, out AVSResult, out CVV2Response, out processorTransactionID);
                }
                if (transactionTypeCode == "B" && transactionStatusCode == "A")
                {
                    processorTransactionID = rdr.GetString(15);
                    authCode = rdr.GetString(16);
                    result = processor.DepositWithPriorAuth(ci, transactionID.Value, rdr.GetDecimal(3), processorTransactionID, authCode, out responseCode, out responseText, out AVSResult, out CVV2Response);
                }
                if (transactionTypeCode == "R")
                {
                    result = processor.Refund(ci, transactionID.Value, rdr.GetDecimal(3), rdr.GetString(18), out responseCode, out responseText);
                }

                // Make sure that the transaction response is in the codeTransactionResponse table
                cmm.CommandText = "insert into codeTransactionResponse ( ProcessorCode, ResponseCode, Description, Approved, Retry )\n" +
                    "select @ProcessorCode, @ResponseCode, @Description, @Approved, 0\n" +
                    "where not exists ( select 1 from codeTransactionResponse where ProcessorCode = @ProcessorCode and ResponseCode = @ResponseCode";
                cmm.Parameters.Clear();
                cmm.Parameters.Add(new SqlParameter("@ProcessorCode", processorCode));
                cmm.Parameters.Add(new SqlParameter("@ResponseCode", responseCode));
                cmm.Parameters.Add(new SqlParameter("@Description", responseText));
                cmm.Parameters.Add(new SqlParameter("@Approved", (result)?1 : 0));
                cmm.ExecuteNonQuery();

                // Create a new tblTransactionSubmit record
                cmm.CommandText = "insert into tblTransactionSubmit ( TransactionID, TransactioResponseID, TransactionSubmitTypeCode, AVSResponse, CVV2Response, CreateDate, ResponseDate, SubmissionCount )\n" +
                    "select @TransactionID, TransactionResponseID, 'D', @AVSResponse, @CVV2Response, getdate(), getdate(), 0\n" +
                    "from codeTransactionResponse \n" +
                    "where ProcessorCode = @ProcessorCode and ResponseCode = @ResponseCode";
                cmm.Parameters.Clear();
                cmm.Parameters.Add(new SqlParameter("@TransactionID", transactionID.Value));
                cmm.Parameters.Add(new SqlParameter("@AvsResponse", AVSResult));
                cmm.Parameters.Add(new SqlParameter("@CVV2Response", CVV2Response));
                cmm.Parameters.Add(new SqlParameter("@ProcessorCode", processorCode));

                if (result)
                {
                    cmm.CommandText = "update tblTransaction set TransactionStatusCode = 'P',\n" +
                        "   ProcessorTransactionID=@ProcessorTransactionID,\n" +
                        "where TransactionID = @TransactionID";
                    cmm.Parameters.Clear();
                    cmm.Parameters.Add(new SqlParameter("@ProcessorTransactionID", processorTransactionID));
                    cmm.Parameters.Add(new SqlParameter("@CardAuthorization", authCode));
                    cmm.Parameters.Add(new SqlParameter("@TransactionID", transactionID.Value));
                    cmm.ExecuteNonQuery();
                }
                else if (failTransactionOnFailure.Value)
                {
                    cmm.CommandText = "update tblTransaction set TransactionStatusCode = 'F' where TransactionID = @TransactionID";
                    cmm.Parameters.Clear();
                    cmm.Parameters.Add(new SqlParameter("@TransactionID", transactionID.Value));
                    cmm.ExecuteNonQuery();
                }
            }
        }


        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void AuthorizeTransaction(SqlInt32 transactionID, SqlString cvv2, SqlBoolean failTransactionOnFailure)
        {
            SqlConnection conn = new SqlConnection(@"context connection=true");
            if (conn.State != System.Data.ConnectionState.Open)
                conn.Open();

            SqlCommand cmm = conn.CreateCommand();
            cmm.CommandText = "select tr.TransactionID, tr.TransactionTypeCode, tr.ProcessorCode, tr.Amount, acc.CardNumber, acc.CardExpiration, oh.OwnerID,\n" +
                "   per.FirstName, per.LastName, add.Address1, add.City, add.State, add.PostalCode, cou.Name\n" +
                "from tblTransaction tr\n" +
                "   join tblAccount acc on tr.AccountID = acc.AccountID\n" +
                "   join tblOrderHeader oh on tr.OrderID = oh.OrderID\n" +
                "   join tblCustomer cus on oh.CustomerID = cus.CustomerID\n" +
                "   join tblPerson per on cus.PersonID = per.PersonID\n" +
                "   join tblAddress add on per.AddressID = add.AddressID\n" +
                "   join codeCountry cou on add.CountryID = cou.CountryID\n" +
                "where tr.TransactionStatusCode = 'E'\n" +
                "   and tr.TransactionType = 'B'\n" +
                "   and tr.TransactionID = " + transactionID.Value.ToString();
            SqlDataReader rdr = cmm.ExecuteReader();
            if (rdr.Read())
            {
                CustomerInfo ci = new CustomerInfo();
                ci.FirstName = rdr.GetString(7);
                ci.LastName = rdr.GetString(8);
                ci.Address = rdr.GetString(9);
                ci.City = rdr.GetString(10);
                ci.State = rdr.GetString(11);
                ci.Zip = rdr.GetString(12);
                ci.Country = rdr.GetString(13);
                ci.CardNumber = rdr.GetString(4);
                ci.CardExpiration = rdr.GetString(5);
                ci.CVV2 = cvv2.Value;

                string processorCode = rdr.GetString(2);
                int ownerID = rdr.GetInt32(6);
                PaymentProcessor processor = CreatePaymentProcessor(conn, processorCode[0], ownerID);
                string responseCode, responseText, AVSResult, CVV2Response, processorTransactionID, authCode;
                bool result = processor.Authorize(ci, rdr.GetInt32(0), rdr.GetDecimal(3), out responseCode, out responseText, out AVSResult, out CVV2Response, out processorTransactionID, out authCode );

                // Make sure that the transaction response is in the codeTransactionResponse table
                cmm.CommandText = "insert into codeTransactionResponse ( ProcessorCode, ResponseCode, Description, Approved, Retry )\n" +
                    "select @ProcessorCode, @ResponseCode, @Description, @Approved, 0\n" +
                    "where not exists ( select 1 from codeTransactionResponse where ProcessorCode = @ProcessorCode and ResponseCode = @ResponseCode";
                cmm.Parameters.Clear();
                cmm.Parameters.Add(new SqlParameter("@ProcessorCode", processorCode));
                cmm.Parameters.Add(new SqlParameter("@ResponseCode", responseCode));
                cmm.Parameters.Add(new SqlParameter("@Description", responseText));
                cmm.Parameters.Add(new SqlParameter("@Approved", (result)?1 : 0));
                cmm.ExecuteNonQuery();

                // Create a new tblTransactionSubmit record
                cmm.CommandText = "insert into tblTransactionSubmit ( TransactionID, TransactioResponseID, TransactionSubmitTypeCode, AVSResponse, CVV2Response, CreateDate, ResponseDate, SubmissionCount )\n" +
                    "select @TransactionID, TransactionResponseID, 'A', @AVSResponse, @CVV2Response, getdate(), getdate(), 0\n" +
                    "from codeTransactionResponse \n" +
                    "where ProcessorCode = @ProcessorCode and ResponseCode = @ResponseCode";
                cmm.Parameters.Clear();
                cmm.Parameters.Add(new SqlParameter("@TransactionID", transactionID.Value));
                cmm.Parameters.Add(new SqlParameter("@AvsResponse", AVSResult));
                cmm.Parameters.Add(new SqlParameter("@CVV2Response", CVV2Response));
                cmm.Parameters.Add(new SqlParameter("@ProcessorCode", processorCode));

                if (result)
                {
                    cmm.CommandText = "update tblTransaction set TransactionStatusCode = 'A',\n" +
                        "   ProcessorTransactionID=@ProcessorTransactionID,\n" +
                        "   CardAuthorization=@CardAuthorization\n" +
                        "   CardAuthorizationDate=getdate()\n" +
                        "where TransactionID = @TransactionID";
                    cmm.Parameters.Clear();
                    cmm.Parameters.Add(new SqlParameter("@ProcessorTransactionID", processorTransactionID));
                    cmm.Parameters.Add(new SqlParameter("@CardAuthorization", authCode));
                    cmm.Parameters.Add(new SqlParameter("@TransactionID", transactionID.Value));
                    cmm.ExecuteNonQuery();
                }
                else if (failTransactionOnFailure.Value)
                {
                    cmm.CommandText = "update tblTransaction set TransactionStatusCode = 'F' where TransactionID = @TransactionID";
                    cmm.Parameters.Clear();
                    cmm.Parameters.Add(new SqlParameter("@TransactionID", transactionID.Value));
                    cmm.ExecuteNonQuery();
                }
            }

        }

        private static PaymentProcessor CreatePaymentProcessor(SqlConnection conn, char processorCode, int ownerID)
        {
            SqlCommand cmm = conn.CreateCommand();
            cmm.CommandText = "select ClassName from codePaymentProcessor where ProcessorCode = '" + processorCode;
            SqlDataReader rdr = cmm.ExecuteReader();
            if (!rdr.Read())
                throw new Exception("Missing ClassName for payment system " + processorCode);
            string className = rdr.GetString(0);
            rdr.Close();

            ObjectHandle handle = System.Activator.CreateInstance(null,
                (string)className,
                true,
                0,
                null,
                new object[] { processorCode, ownerID },
                null, null, null);

            if (handle == null)
                throw new Exception("Error creating payment processor class " + className);
            return (PaymentProcessor)handle.Unwrap();
        }

		public PaymentProcessor( char processorCode, int ownerID ) 
		{
            _conn = new SqlConnection(@"context connection=true");
            _conn.Open();

            SqlCommand cmm = _conn.CreateCommand();
            cmm.CommandText = "select Name, dbo.Decrypt(Value) from tblPaymentProcessorConfig where ProcessorCode = '" + processorCode + "' and OwnerID = " + ownerID.ToString();
            SqlDataReader rdr = cmm.ExecuteReader();
            _configStrings = new Dictionary<string, string>();
            while (rdr.Read())
                _configStrings.Add(rdr.GetString(0), rdr.GetString(1));
            rdr.Close();

            _test = _configStrings.ContainsKey("Test");
        }

		public virtual bool Authorize( CustomerInfo customerInfo, 
			Int32 transactionID, 
			decimal amount, 
			out string responseCode,
			out string responseText,
			out string AVSResult,
			out string CVV2Response,
			out string processorTransactionID,
			out string authCode 
			)
		{
			return _paymentProcessor.Authorize( 
				customerInfo, 
				transactionID, 
				amount, 
				out responseCode, 
				out responseText, 
				out AVSResult,
				out CVV2Response,
				out processorTransactionID,
				out authCode );
		}

		public virtual bool Deposit( CustomerInfo customerInfo, 
			Int32 transactionID, 
			decimal amount, 
			out string responseCode,
			out string responseText,
			out string AVSResult,
			out string CVV2Response,
			out string processorTransactionID 
			)
		{
			return _paymentProcessor.Deposit(
				customerInfo, 
				transactionID, 
				amount, 
				out responseCode,
				out responseText,
				out AVSResult,
				out CVV2Response,
				out processorTransactionID );
		}

		public virtual bool DepositWithPriorAuth( CustomerInfo customerInfo, 
			Int32 transactionID, 
			decimal amount, 
			string processorTransactionID,
			string authCode,
			out string responseCode,
			out string responseText,
			out string AVSResult,
			out string CVV2Response
			)
		{
			return _paymentProcessor.DepositWithPriorAuth(
				customerInfo,
				transactionID, 
				amount, 
				processorTransactionID,
				authCode,
				out responseCode,
				out responseText,
				out AVSResult,
				out CVV2Response );
		}

		public virtual bool Refund( CustomerInfo customerInfo, 
			Int32 transactionID, 
			decimal amount, 
			string processorTransactionID,
			out string responseCode,
			out string responseText
			)
		{
			return _paymentProcessor.Refund(
				customerInfo, 
				transactionID, 
				amount, 
				processorTransactionID,
				out responseCode,
				out responseText );
		}
	}
}
